--    Program: 		CAPARIMP_PAYMENT_METHOD.sql
--    Description:  Import AR Payment Method Account information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
                      		                 		
   cursor cur_pay_meth is
      select * from CRST_AR_RECEIPT_METHOD_ACCOUNT;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_pay_meth loop  
   	 l_err_msg_tmp := '';
   	 
   	 begin 
   	   -- import data
       insert into AR_RECEIPT_METHOD_ACCOUNTS_ALL
       (  
         BANK_ACCOUNT_ID                 ,
         RECEIPT_METHOD_ID               ,
         CASH_CCID                       ,
         CREATED_BY                      ,
         CREATION_DATE                   ,
         EARNED_CCID                     ,
         LAST_UPDATED_BY                 ,
         LAST_UPDATE_DATE                ,
         ON_ACCOUNT_CCID                 ,
         START_DATE                      ,
         UNAPPLIED_CCID                  ,
         UNEARNED_CCID                   ,
         UNIDENTIFIED_CCID               ,
         BANK_CHARGES_CCID               ,
         END_DATE                        ,
         FACTOR_CCID                     ,
         FACTOR_PRINT_PROGRAM_ID         ,
         FACTOR_TRANSMISSION_PROGRAM_ID  ,
         LAST_UPDATE_LOGIN               ,
         MIN_RECEIPT_AMOUNT              ,
         RECEIPT_CLEARING_CCID           ,
         REMITTANCE_CCID                 ,
         REMIT_PRINT_PROGRAM_ID          ,
         REMIT_TRANSMISSION_PROGRAM_ID   ,
         ATTRIBUTE_CATEGORY              ,
         ATTRIBUTE1                      ,
         ATTRIBUTE2                      ,
         ATTRIBUTE3                      ,
         ATTRIBUTE4                      ,
         ATTRIBUTE5                      ,
         ATTRIBUTE6                      ,
         ATTRIBUTE7                      ,
         ATTRIBUTE8                      ,
         ATTRIBUTE9                      ,
         ATTRIBUTE10                     ,
         ATTRIBUTE11                     ,
         ATTRIBUTE12                     ,
         ATTRIBUTE13                     ,
         ATTRIBUTE14                     ,
         ATTRIBUTE15                     ,
         PRIMARY_FLAG                    ,
         CLEARING_DAYS                   ,
         SHORT_TERM_DEBT_CCID            ,
         OVERRIDE_REMIT_ACCOUNT_FLAG     ,
         RISK_ELIMINATION_DAYS           ,
         ORG_ID                          ,
         GLOBAL_ATTRIBUTE1               ,
         GLOBAL_ATTRIBUTE2               ,
         GLOBAL_ATTRIBUTE3               ,
         GLOBAL_ATTRIBUTE4               ,
         GLOBAL_ATTRIBUTE5               ,
         GLOBAL_ATTRIBUTE6               ,
         GLOBAL_ATTRIBUTE7               ,
         GLOBAL_ATTRIBUTE8               ,
         GLOBAL_ATTRIBUTE9               ,
         GLOBAL_ATTRIBUTE10              ,
         GLOBAL_ATTRIBUTE11              ,
         GLOBAL_ATTRIBUTE12              ,
         GLOBAL_ATTRIBUTE13              ,
         GLOBAL_ATTRIBUTE14              ,
         GLOBAL_ATTRIBUTE15              ,
         GLOBAL_ATTRIBUTE16              ,
         GLOBAL_ATTRIBUTE17              ,
         GLOBAL_ATTRIBUTE18              ,
         GLOBAL_ATTRIBUTE19              ,
         GLOBAL_ATTRIBUTE20              ,
         GLOBAL_ATTRIBUTE_CATEGORY       ,
         EDISC_RECEIVABLES_TRX_ID        ,
         UNEDISC_RECEIVABLES_TRX_ID      ,
         BR_COLLECTION_DAYS              ,
         BR_STD_RECEIVABLES_TRX_ID       ,
         BR_REMITTANCE_CCID              ,
         BR_FACTOR_CCID
       )
       values
       (
         CAPBRIMP_MAPPING.GetBankACMapping(cur.BANK_ACCOUNT_ID),
         cur.RECEIPT_METHOD_ID               ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.CASH_CCID),
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.CREATION_DATE                   ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.EARNED_CCID),
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.LAST_UPDATE_DATE                ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.ON_ACCOUNT_CCID),
         cur.START_DATE                      ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.UNAPPLIED_CCID),
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.UNEARNED_CCID),
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.UNIDENTIFIED_CCID),
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.BANK_CHARGES_CCID),
         cur.END_DATE                        ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.FACTOR_CCID),
         cur.FACTOR_PRINT_PROGRAM_ID         ,
         cur.FACTOR_TRANSMISSION_PROGRAM_ID  ,
         cur.LAST_UPDATE_LOGIN               ,
         cur.MIN_RECEIPT_AMOUNT              ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.RECEIPT_CLEARING_CCID),
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.REMITTANCE_CCID),
         cur.REMIT_PRINT_PROGRAM_ID          ,
         cur.REMIT_TRANSMISSION_PROGRAM_ID   ,
         cur.ATTRIBUTE_CATEGORY              ,
         cur.ATTRIBUTE1                      ,
         cur.ATTRIBUTE2                      ,
         cur.ATTRIBUTE3                      ,
         cur.ATTRIBUTE4                      ,
         cur.ATTRIBUTE5                      ,
         cur.ATTRIBUTE6                      ,
         cur.ATTRIBUTE7                      ,
         cur.ATTRIBUTE8                      ,
         cur.ATTRIBUTE9                      ,
         cur.ATTRIBUTE10                     ,
         cur.ATTRIBUTE11                     ,
         cur.ATTRIBUTE12                     ,
         cur.ATTRIBUTE13                     ,
         cur.ATTRIBUTE14                     ,
         cur.ATTRIBUTE15                     ,
         cur.PRIMARY_FLAG                    ,
         cur.CLEARING_DAYS                   ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.SHORT_TERM_DEBT_CCID),
         cur.OVERRIDE_REMIT_ACCOUNT_FLAG     ,
         cur.RISK_ELIMINATION_DAYS           ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.GLOBAL_ATTRIBUTE1               ,
         cur.GLOBAL_ATTRIBUTE2               ,
         cur.GLOBAL_ATTRIBUTE3               ,
         cur.GLOBAL_ATTRIBUTE4               ,
         cur.GLOBAL_ATTRIBUTE5               ,
         cur.GLOBAL_ATTRIBUTE6               ,
         cur.GLOBAL_ATTRIBUTE7               ,
         cur.GLOBAL_ATTRIBUTE8               ,
         cur.GLOBAL_ATTRIBUTE9               ,
         cur.GLOBAL_ATTRIBUTE10              ,
         cur.GLOBAL_ATTRIBUTE11              ,
         cur.GLOBAL_ATTRIBUTE12              ,
         cur.GLOBAL_ATTRIBUTE13              ,
         cur.GLOBAL_ATTRIBUTE14              ,
         cur.GLOBAL_ATTRIBUTE15              ,
         cur.GLOBAL_ATTRIBUTE16              ,
         cur.GLOBAL_ATTRIBUTE17              ,
         cur.GLOBAL_ATTRIBUTE18              ,
         cur.GLOBAL_ATTRIBUTE19              ,
         cur.GLOBAL_ATTRIBUTE20              ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
         cur.EDISC_RECEIVABLES_TRX_ID        ,
         cur.UNEDISC_RECEIVABLES_TRX_ID      ,
         cur.BR_COLLECTION_DAYS              ,
         cur.BR_STD_RECEIVABLES_TRX_ID       ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.BR_REMITTANCE_CCID),
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.BR_FACTOR_CCID)
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;    
     
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_PAYMENT_METHOD', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit